package com.cl.core.builder.handler;

import com.alibaba.fastjson.JSON;
import com.cl.core.builder.Enum.FieldTypeEnum;
import com.cl.core.builder.Enum.MockTypeEnum;
import com.cl.core.builder.model.sql.Field;
import com.cl.core.builder.model.sql.SQLEntity;
import com.cl.core.builder.result.SQLResult;
import com.cl.utility.constant.MockRandomValue;
import com.cl.utility.pojo.Student;
import com.cl.utility.util.BeanUtils;
import com.cl.utility.util.FileUtils;
import com.cl.utility.util.GenerateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;

import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @author liddhappy
 * @description sql 处理器
 * @createTime 2022/12/28 19:19
 */
@Slf4j
public class SQLHandler extends BuilderHandler {

    public SQLHandler(String builderConfig, String target, SQLEntity sqlEntity, List<T> results) {
        super(builderConfig, target, sqlEntity, results);
    }

    @Override
    public void processBuilder() throws Exception {
        switch (target) {
            case "create_table":
                SQLResult createSQLResult = new SQLResult();
                String createTableSQL = createTableSQL((SQLEntity) builderModel);
                createSQLResult.setResultCode(GenerateUtils.getRandomValue(MockRandomValue.UUID));
                createSQLResult.setDescription("创表语句");
                createSQLResult.setSQLDialect(createTableSQL);
                results.add(createSQLResult);
                break;
            case "insert_table":
                SQLResult insertSQLResult = new SQLResult();
                List<Map<String, Object>> insertDataList = fromJsonToDataList();
                String insertTableSQL = buildInsertSql((SQLEntity) builderModel, insertDataList);
                insertSQLResult.setResultCode(GenerateUtils.getRandomValue(MockRandomValue.UUID));
                insertSQLResult.setDescription("插入表语句");
                insertSQLResult.setSQLDialect(insertTableSQL);
                results.add(insertSQLResult);
                break;
            case "update_table":
                SQLResult updateSQLResult = new SQLResult();
                List<Map<String, Object>> updateDataList = fromJsonToDataList();
                String updateTableSQL = buildUpdateSql((SQLEntity) builderModel, updateDataList);
                updateSQLResult.setResultCode(GenerateUtils.getRandomValue(MockRandomValue.UUID));
                updateSQLResult.setDescription("更新表语句");
                updateSQLResult.setSQLDialect(updateTableSQL);
                results.add(updateSQLResult);
                break;
            case "delete_table":
                SQLResult deleteSQLResult = new SQLResult();
                List<Map<String, Object>> deleteDataList = fromJsonToDataList();
                String deleteTableSql = buildDeleteSql((SQLEntity) builderModel, deleteDataList);
                deleteSQLResult.setResultCode(GenerateUtils.getRandomValue(MockRandomValue.UUID));
                deleteSQLResult.setDescription("删除表语句");
                deleteSQLResult.setSQLDialect(deleteTableSql);
                results.add(deleteSQLResult);
                break;
            case "select_table":
                SQLResult selectSQLResult = new SQLResult();
                List<Map<String, Object>> selectDataList = fromJsonToDataList();
                String selectTableSql = buildSelectSql((SQLEntity) builderModel, selectDataList);
                selectSQLResult.setResultCode(GenerateUtils.getRandomValue(MockRandomValue.UUID));
                selectSQLResult.setDescription("查询表语句");
                selectSQLResult.setSQLDialect(selectTableSql);
                results.add(selectSQLResult);
                break;
            default:
                break;
        }

    }

    /**
     * @param sqlEntity 实体
     * @return
     */
    private String createTableSQL(SQLEntity sqlEntity) {
        // 构造模板
        String template = "create table if not exists %s\n"
                + "(\n"
                + "%s\n"
                + ") %s;";
        String dbName = sqlEntity.getDbName();
        String tableName = sqlEntity.getTableName();
        // 构造表名
        if (StringUtils.isNotBlank(dbName)) {
            tableName = String.format("%s.%s", dbName, tableName);
        }
        // 构造表字段
        List<Field> fieldList = sqlEntity.getFieldList();
        StringBuilder fieldStrBuilder = new StringBuilder();
        int fieldSize = fieldList.size();
        for (int i = 0; i < fieldSize; i++) {
            Field field = fieldList.get(i);
            fieldStrBuilder.append(buildCreateFieldSql(field));
            // 最后一个字段后没有逗号和换行
            if (i != fieldSize - 1) {
                fieldStrBuilder.append(",");
                fieldStrBuilder.append("\n");
            }
        }
        String fieldStr = fieldStrBuilder.toString();
        String tableComment = sqlEntity.getTableComment();
        // 填充模板
        String sql = String.format(template, tableName, fieldStr, tableComment);
        log.info("createTableSQL SQL = \n" + sql);
        return sql;
    }

    private List<Map<String, Object>> fromJsonToDataList() throws Exception {
        String[] split = builderConfig.split(";");
        String fileFolder = split[0];
        String fileName = split[1];
        String resourceJsonFileContent = FileUtils.getResourceJsonFileContent(fileFolder, fileName);
        List<Student> students = JSON.parseArray(resourceJsonFileContent, Student.class);
        //student 参数所有参数名
        Student studentT = new Student();
        List<String> fieldNames =
                Stream.of(BeanUtils.getAllFields(studentT)).map(java.lang.reflect.Field::getName).collect(Collectors.toList());
        if (fieldNames.contains("serialVersionUID")) {
            fieldNames.remove("serialVersionUID");
        }
        List<Map<String, Object>> dataList = new ArrayList<>();
        for (Student student : students) {
            Map<String, Object> map = new HashMap<>();
            for (String fieldName : fieldNames) {
                map.put(fieldName, BeanUtils.getFieldValue(student, fieldName));
            }
            dataList.add(map);
        }
        return dataList;
    }

    /**
     * 生成创建字段的 SQL
     *
     * @param field 字段
     * @return
     */
    public String buildCreateFieldSql(Field field) {
        String fieldName = field.getFieldName();
        String fieldType = field.getFieldType();
        String defaultValue = field.getDefaultValue();
        String otherValue = field.getOtherValue();
        boolean notNull = field.isNotNull();
        String comment = field.getComment();
        boolean primaryKey = field.isPrimaryKey();
        boolean autoIncrement = field.isAutoIncrement();
        StringBuilder fieldStrBuilder = new StringBuilder();
        // 字段名
        fieldStrBuilder.append(fieldName);
        // 字段类型
        if (fieldType.equalsIgnoreCase(FieldTypeEnum.VARCHAR.getValue()) && StringUtils.isNotBlank(otherValue)) {
            fieldStrBuilder.append(" ").append(fieldType).append("(").append(Integer.parseInt(otherValue)).append(")");
        } else {
            fieldStrBuilder.append(" ").append(fieldType);
        }
        // 默认值
        if (StringUtils.isNotBlank(defaultValue)) {
            fieldStrBuilder.append(" ").append("default ").append(getValueStr(field, defaultValue));
        }
        // 是否非空
        fieldStrBuilder.append(" ").append(notNull ? "not null" : "null");
        // 是否自增
        if (autoIncrement) {
            fieldStrBuilder.append(" ").append("auto_increment");
        }
        // 注释
        if (StringUtils.isNotBlank(comment)) {
            fieldStrBuilder.append(" ").append(String.format("comment '%s'", comment));
        }
        // 是否为主键
        if (primaryKey) {
            fieldStrBuilder.append(" ").append("primary key");
        }
        return fieldStrBuilder.toString();
    }

    /**
     * 构造插入数据 SQL
     *
     * @param sqlEntity 实体
     * @param dataList  数据列表
     * @return 生成的 SQL 列表字符串
     */
    public String buildInsertSql(SQLEntity sqlEntity, List<Map<String, Object>> dataList) {
        // 构造模板
        String template = "insert into %s (%s) values (%s);";
        // 构造表名
        String tableName = sqlEntity.getTableName();
        String dbName = sqlEntity.getDbName();
        if (StringUtils.isNotBlank(dbName)) {
            tableName = String.format("%s.%s", dbName, tableName);
        }
        // 构造表字段
        List<Field> fieldList = sqlEntity.getFieldList();
        // 过滤掉不模拟的字段
        fieldList = fieldList.stream()
                .filter(field -> {
                    MockTypeEnum mockTypeEnum = Optional.ofNullable(MockTypeEnum.getEnumByValue(field.getMockType()))
                            .orElse(MockTypeEnum.NONE);
                    return !MockTypeEnum.NONE.equals(mockTypeEnum);
                })
                .collect(Collectors.toList());
        StringBuilder resultStringBuilder = new StringBuilder();
        for (int i = 0; i < dataList.size(); i++) {
            Map<String, Object> dataRow = dataList.get(i);
            String keyStr = fieldList.stream()
                    .map(Field::getFieldName)
                    .collect(Collectors.joining(", "));
            String valueStr = fieldList.stream()
                    .map(field -> getValueStr(field, dataRow.get(field.getFieldName())))
                    .collect(Collectors.joining(", "));
            // 填充模板
            String result = String.format(template, tableName, keyStr, valueStr);
            resultStringBuilder.append(result);
            // 最后一个字段后没有换行
            if (i != dataList.size() - 1) {
                resultStringBuilder.append("\n");
            }
        }
        log.info("buildInsertSql SQL = \n" + resultStringBuilder.toString());
        return resultStringBuilder.toString();
    }

    public String buildUpdateSql(SQLEntity sqlEntity, List<Map<String, Object>> dataList) {
        String template = "update %s set %s where %s";
        // 构造表名
        String tableName = sqlEntity.getTableName();
        String dbName = sqlEntity.getDbName();
        if (StringUtils.isNotBlank(dbName)) {
            tableName = String.format("%s.%s", dbName, tableName);
        }
        // 构造表字段
        List<Field> fieldList = sqlEntity.getFieldList();
        // 过滤掉不模拟的字段
        fieldList = fieldList.stream()
                .filter(field -> {
                    MockTypeEnum mockTypeEnum = Optional.ofNullable(MockTypeEnum.getEnumByValue(field.getMockType()))
                            .orElse(MockTypeEnum.NONE);
                    return !MockTypeEnum.NONE.equals(mockTypeEnum);
                })
                .collect(Collectors.toList());
        StringBuilder resultStringBuilder = new StringBuilder();
        List<String> primaryKeys =
                fieldList.stream().filter(Field::isPrimaryKey).map(Field::getFieldName).collect(Collectors.toList());
        List<String> fieldString = new ArrayList<>();
        List<String> primaryKeyString = new ArrayList<>();
        for (int i = 0; i < dataList.size(); i++) {
            Map<String, Object> dataRow = dataList.get(i);
            for (Map.Entry<String, Object> stringObjectEntry : dataRow.entrySet()) {
                String key = stringObjectEntry.getKey();
                Object value = stringObjectEntry.getValue();
                if (primaryKeys.contains(key)) {
                    String primaryString = buildFieldFormat(key, value);
                    primaryKeyString.add(primaryString);
                }
                String fieldSql = buildFieldFormat(key, value);
                fieldString.add(fieldSql);
            }
            List<String> otherFieldString =
                    fieldString.stream().filter(E -> !primaryKeyString.contains(E)).collect(Collectors.toList());
            // 填充模板
            String result = String.format(template, tableName, listToString(otherFieldString),
                    listToString(primaryKeyString) + ";");
            resultStringBuilder.append(result);
            fieldString.clear();
            primaryKeyString.clear();
            // 最后一个字段后没有换行
            if (i != dataList.size() - 1) {
                resultStringBuilder.append("\n");
            }
        }
        log.info("buildUpdateSql SQL = \n" + resultStringBuilder.toString());
        return resultStringBuilder.toString();
    }

    public String buildDeleteSql(SQLEntity sqlEntity, List<Map<String, Object>> dataList) {
        String template = "delete from %s where %s";
        // 构造表名
        String tableName = sqlEntity.getTableName();
        String dbName = sqlEntity.getDbName();
        if (StringUtils.isNotBlank(dbName)) {
            tableName = String.format("%s.%s", dbName, tableName);
        }
        // 构造表字段
        List<Field> fieldList = sqlEntity.getFieldList();
        // 过滤掉不模拟的字段
        fieldList = fieldList.stream()
                .filter(field -> {
                    MockTypeEnum mockTypeEnum = Optional.ofNullable(MockTypeEnum.getEnumByValue(field.getMockType()))
                            .orElse(MockTypeEnum.NONE);
                    return !MockTypeEnum.NONE.equals(mockTypeEnum);
                })
                .collect(Collectors.toList());
        StringBuilder resultStringBuilder = new StringBuilder();
        List<String> primaryKeys =
                fieldList.stream().filter(Field::isPrimaryKey).map(Field::getFieldName).collect(Collectors.toList());
        List<String> primaryKeyString = new ArrayList<>();
        for (int i = 0; i < dataList.size(); i++) {
            Map<String, Object> dataRow = dataList.get(i);
            for (Map.Entry<String, Object> stringObjectEntry : dataRow.entrySet()) {
                String key = stringObjectEntry.getKey();
                Object value = stringObjectEntry.getValue();
                if (primaryKeys.contains(key)) {
                    String primaryString = buildFieldFormat(key, value);
                    primaryKeyString.add(primaryString);
                }
            }

            // 填充模板
            String result = String.format(template, tableName, listToString(primaryKeyString) + ";");
            resultStringBuilder.append(result);
            primaryKeyString.clear();
            // 最后一个字段后没有换行
            if (i != dataList.size() - 1) {
                resultStringBuilder.append("\n");
            }
        }
        log.info("buildDeleteSql SQL = \n" + resultStringBuilder.toString());
        return resultStringBuilder.toString();
    }

    public String buildSelectSql(SQLEntity sqlEntity, List<Map<String, Object>> dataList) {
        String template = "select * from %s where %s";
        // 构造表名
        String tableName = sqlEntity.getTableName();
        String dbName = sqlEntity.getDbName();
        if (StringUtils.isNotBlank(dbName)) {
            tableName = String.format("%s.%s", dbName, tableName);
        }
        // 构造表字段
        List<Field> fieldList = sqlEntity.getFieldList();
        // 过滤掉不模拟的字段
        fieldList = fieldList.stream()
                .filter(field -> {
                    MockTypeEnum mockTypeEnum = Optional.ofNullable(MockTypeEnum.getEnumByValue(field.getMockType()))
                            .orElse(MockTypeEnum.NONE);
                    return !MockTypeEnum.NONE.equals(mockTypeEnum);
                })
                .collect(Collectors.toList());
        StringBuilder resultStringBuilder = new StringBuilder();
        List<String> primaryKeys =
                fieldList.stream().filter(Field::isPrimaryKey).map(Field::getFieldName).collect(Collectors.toList());
        List<String> primaryKeyString = new ArrayList<>();
        for (int i = 0; i < dataList.size(); i++) {
            Map<String, Object> dataRow = dataList.get(i);
            for (Map.Entry<String, Object> stringObjectEntry : dataRow.entrySet()) {
                String key = stringObjectEntry.getKey();
                Object value = stringObjectEntry.getValue();
                if (primaryKeys.contains(key)) {
                    String primaryString = buildFieldFormat(key, value);
                    primaryKeyString.add(primaryString);
                }
            }
            // 填充模板
            String result = String.format(template, tableName, listToString(primaryKeyString) + ";");
            resultStringBuilder.append(result);
            primaryKeyString.clear();
            // 最后一个字段后没有换行
            if (i != dataList.size() - 1) {
                resultStringBuilder.append("\n");
            }
        }
        log.info("buildSelectSql SQL = \n" + resultStringBuilder.toString());
        return resultStringBuilder.toString();
    }

    private String buildFieldFormat(String key, Object value) {
        String result = null;
        if (StringUtils.isNoneBlank(key) && ObjectUtils.isNotEmpty(value)) {
            result = "`" + key + "`" + "=" + "'" + value + "',";
        }
        return result;
    }

    private String listToString(List<String> list) {
        StringBuffer stringBuffer = new StringBuffer();
        list.forEach(stringBuffer::append);
        return stringBuffer.toString().substring(0, stringBuffer.toString().lastIndexOf(','));
    }

    /**
     * 根据列的属性获取值字符串
     *
     * @param field
     * @param value
     * @return
     */
    public static String getValueStr(Field field, Object value) {
        if (field == null || value == null) {
            return "''";
        }
        FieldTypeEnum fieldTypeEnum = Optional.ofNullable(FieldTypeEnum.getEnumByValue(field.getFieldType()))
                .orElse(FieldTypeEnum.TEXT);
        String result = String.valueOf(value);
        switch (fieldTypeEnum) {
            case DATETIME:
            case TIMESTAMP:
                return result.equalsIgnoreCase("CURRENT_TIMESTAMP") ? result : String.format("'%s'", value);
            case DATE:
            case TIME:
            case CHAR:
            case VARCHAR:
            case TINYTEXT:
            case TEXT:
            case MEDIUMTEXT:
            case LONGTEXT:
            case TINYBLOB:
            case BLOB:
            case MEDIUMBLOB:
            case LONGBLOB:
            case BINARY:
            case VARBINARY:
                return String.format("'%s'", value);
            default:
                return result;
        }
    }
}
